IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_MediaSelectOne]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_MediaSelectOne]
GO
create procedure [dbo].[cms_sp_MediaSelectOne]
(
@SiteGroupId int,
@SiteId int,
@MediaId int
)
as
declare @result table
(
MediaContentId int not null,
SiteId int null,
SiteGroupId int null,
MediaId int not null,
MediaContentName nvarchar(250) not null,
MetaCode nvarchar(150) not null,
Description nvarchar(500) null,
CreatedBy int not null,
DateCreated datetime not null,
LastUpdatedBy int null,
DateLastUpdated datetime null,
MediaFileId int not null,
IsPrimary bit not null,
MimeTypeId int null,
ContentType nvarchar(250) null,
MimeTypeName nvarchar(150) null,
IsImage bit null,
LinkedFileName nvarchar(250) not null,
OriginalFileName nvarchar(250) not null,
FileSize bigint not null,
Height int null,
Width int null
)
select 
	MediaId,
	SiteId,
	SiteGroupId,
	FolderName,
	Description,
	CreatedBy,
	DateCreated,
	LastUpdatedBy,
	DateLastUpdated
from dbo.Media
where MediaId = @MediaId and
	(([SiteGroupId] is null and [SiteId] is null) or 
	([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
	([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId))
insert into @result
(
MediaContentId,
SiteId,
SiteGroupId,
MediaId,
MediaContentName,
MetaCode,
Description,
CreatedBy,
DateCreated,
LastUpdatedBy,
DateLastUpdated,
MediaFileId,
IsPrimary,
MimeTypeId,
ContentType,
MimeTypeName,
IsImage,
LinkedFileName,
OriginalFileName,
FileSize,
Height,
Width
)
select 
	MediaContents.MediaContentId,
	SiteId,
	SiteGroupId,
	MediaId,
	MediaContentName,
	MetaCode,
	Description,
	CreatedBy,
	DateCreated,
	LastUpdatedBy,
	DateLastUpdated,
	Files.MediaFileId,
	MediaContentFileLinks.IsPrimary,
	Files.MimeTypeId,
	Files.ContentType,
	Files.MimeTypeName,
	Files.IsImage,
	Files.LinkedFileName,
	Files.OriginalFileName,
	Files.FileSize,
	Files.Height,
	Files.Width
from dbo.MediaContents
inner join dbo.MediaContentFileLinks
on MediaContentFileLinks.MediaContentId = MediaContents.MediaContentId
inner join 
	(select 
		MediaFiles.MediaFileId,
		MediaFiles.MimeTypeId,
		MimeTypes.MimeTypeName,
		MimeTypes.ContentType,	
		MimeTypes.IsImage,
		MediaFiles.OriginalFileName,
		MediaFiles.LinkedFileName,
		MediaFiles.FileSize,
		MediaFiles.Height,
		MediaFiles.Width
	from dbo.MediaFiles
	left join dbo.MimeTypes
		on MimeTypes.MimeTypeId = MediaFiles.MimeTypeId) as Files
	on Files.MediaFileId = MediaContentFileLinks.MediaFileId
where MediaId = @MediaId and
	(([SiteGroupId] is null and [SiteId] is null) or 
	([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
	([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId))
select 
	distinct(MediaContentId),
	SiteId,
	SiteGroupId,
	MediaId,
	MediaContentName,
	MetaCode,
	Description,
	CreatedBy,
	DateCreated,
	LastUpdatedBy,
	DateLastUpdated
from @result
select 
	MediaContentId,
	MediaFileId,
	IsPrimary,
	MimeTypeId,
	ContentType,
	MimeTypeName,
	IsImage,
	LinkedFileName,
	OriginalFileName,
	FileSize,
	Height,
	Width
from @result


GO

